from openpyxl import load_workbook
import pandas as pd


def open_excel(filename):
    # 加载工作簿
    workbook = load_workbook(filename)

    # 选择活动的工作表
    sheet = workbook.active

    # 读取特定单元格
    cell_value = sheet['A1'].value

    print(cell_value)

    # 遍历所有行
    for row in sheet.iter_rows(values_only=True):
        print(row)


def read_excel_to_dict(file_path):
    # 使用pandas读取Excel文件
    df = pd.read_excel(file_path, sheet_name='Data', header=3)

    df.set_index(df.columns[0], inplace=True)

    # 将DataFrame转换为字典，字典的键是列名，值是列数据
    data_dict = df.to_dict(orient='index')

    return data_dict


def read_excel_to_df(file_path):
    # 使用pandas读取Excel文件
    df = pd.read_excel(file_path, sheet_name='Data', header=3)

    df.set_index(df.columns[0], inplace=True)

    df.pop('Country Code')
    df.pop('Indicator Name')
    df.pop('Indicator Code')
    return df


def read_csv_to_df(file_path):
    # useful_columns = ['Country', 'Year', 'Urban usage.5']
    useful_columns = [0, 1, 67]
    df = pd.read_csv(file_path, usecols=useful_columns)

    # 分别对'Year'和'C'列进行聚合
    grouped_B = df.groupby('Country')['Year'].agg(list).to_dict()
    grouped_C = df.groupby('Country')['Urban usage.5'].agg(list).to_dict()

    # 合并两个字典
    result_dict = {key: (grouped_B[key], grouped_C[key]) for key in grouped_B}
    return result_dict


# 运行应用
if __name__ == '__main__':
    # pop_dict = read_excel_to_dict("D:\\03.SRC\\Dash\\dash_chart01\\API_SP.URB.TOTL.IN.ZS_DS2_en_excel_v2_29.xls")
    read_csv_to_df(".\\share-of-the-population-using-safely-managed-drinking-water-sources.csv")

    pass
